Database Management System

Q161.

Consider a database with three relation instances shown below. The primary keys for the Drivers and Cars relation are did and cid respectively and the records are stored in ascending order of these primary keys as given in the tables. No indexing is available in the database. \overset{\text{D: Drivers relation}}{\begin{array}{|c|l|r|c|}\hline \textbf{did}& \textbf{dname}& \textbf{rating}& \textbf{age} \\\hline 22& \text{Karthikeyan}& 7& 25 \\ \hline 29& \text{Salman}& 1& 33 \\ \hline 31& \text{Boris}& 8& 55 \\\hline 32& \text{Amoldt}& 8& 25 \\\hline 58& \text{Schumacher}& 10& 35 \\\hline 64& \text{Sachin}& 7& 35 \\\hline 71& \text{Senna}& 10& 16 \\\hline 74& \text{Sachin}& 9& 35 \\\hline 85& \text{Rahul}& 3& 25 \\\hline 95& \text{Ralph}& 3& 53 \\\hline \end{array}} \qquad \overset{\text{R: Reserves relation}}{\begin{array}{|c|c|c|}\hline \textbf {did} & \textbf {Cid} & \textbf {day} \\\hline 22 & 101 & 10-10-06 \\ \hline 22 & 102 & 10-10-06\\ \hline 22 & 103 & 08-10-06 \\\hline 22 & 104 & 07-10-06 \\\hline 31 & 102 & 10-11-16 \\\hline 31&103 &06-11-16 \\\hline 31 & 104&12-11-16 \\\hline 64 & 101 &05-09-06 \\\hline 64& 102 & 08-09-06 \\\hline 74 & 103 & 08-09-06 \\\hline \end{array}} \overset{\text{C: Cars relation}}{\begin{array}{|c|c|c|c|}\hline \textbf {Cid} & \textbf {Cname} & \textbf{colour} \\\hline 101 & \text{Renault} & \text{blue} \\ \hline 102 & \text{Renault} & \text{red} \\ \hline 103 & \text{Ferrari} & \text{green} \\\hline 104 & \text{Jaguar} & \text{red} \\\hline \end{array}} What is the output of the following SQL query? select D.dname from Drivers D where D.did in ( select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'red' intersect select R.did from Cars C, Reserves R where R.cid = C.cid and C.colour = 'green' )
GateOverflow

Q162.

Which of the following statements are TRUE about an SQL query? P : An SQL query can contain a HAVING clause even if it does not have a GROUP BY clause Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause R : All attributes used in the GROUP BY clause must appear in the SELECT clause S : Not all attributes used in the GROUP BY clause need to appear in the SELECT clause
GateOverflow

Q163.

Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1,Y=1) is inserted in the table. Let MX and MY denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out? SELECT Y FROM T WHERE X=7;
GateOverflow

Q164.

Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table?Q1 : Select e.empId From employee e Where not exists (Select * From employee s where s.department = "5" and s.salary >=e.salary) Q2 : Select e.empId From employee e Where e.salary > Any (Select distinct salary From employee s Where s.department ="5"
GateOverflow

Q165.

Consider the following relational schema:Student(school-id,sch-roll-no,sname,saddress) School(school-id,sch-name,sch-address,sch-phone) Enrolment(school-id,sch-roll-no,erollno,examname) ExamResult(erollno,examname,marks) What does the following SQL query output?SELECT sch-name, COUNT (*) FROM School C, Enrolment E, ExamResult R WHERE E.school-id = C.school-id AND E.examname = R.examname AND E.erollno = R.erollno AND R.marks = 100 AND S.school-id IN (SELECT school-id FROM student GROUP BY school-id HAVING COUNT (*) > 200) GROUP By school-id
GateOverflow

Q166.

Which one of the following is NOT a part of the ACID properties of database transactions?
GateOverflow

Q167.

Which of the following concurrency control protocol ensures both conflict and free from deadlock? ,
GateOverflow

Q168.

Suppose a database schedule S involves transactions T1,...,Tn. Construct the precedence graph of S with vertices representing the transactions and edges representing the conflicts. If S is serializable, which one of the following orderings of the vertices of the precedence graph is guaranteed to yield a serial schedule?
GateOverflow

Q169.

In a database system, unique time stamps are assigned to each transaction using Lamport's logical clock . Let TS(T_{1}) and TS(T_{2}) be the timestamps of transactions T_{1} and T_{2} respectively. Besides, T_{1} holds a lock on the resource R, and T_{2} has requested a conflicting lock on the same resource R. The following algorithm is used to prevent deadlocks in the database system assuming that a killed transaction is restarted with the same timestamp. Assume any transactions that is not killed terminates eventually. Which of the following is TRUE about the database system that uses the above algorithm to prevent deadlocks?
GateOverflow

Q170.

Let R_i(z) and W_i(z) denote read and write operations on a data element z by a transaction T_i, respectively. Consider the schedule S with four transactions.S: R_4(x)R_2(x)R_3(x)R_1(y)W_1(y)W_2 (x)W_3 (y)R_4(y) Which one of the following serial schedules is conflict equivalent to S?
GateOverflow